package br.com.sgbl.dao;

import br.com.sgbl.bean.Mesa;
import br.com.sgbl.utils.Conexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Diana
 */
public class MesaDao {

    private static final String SQL_LISTAR =
            "SELECT * FROM mesa order by numero";
    private static final String SQL_LOCALIZAR =
            "SELECT * FROM mesa WHERE id = ?";
    private static final String SQL_BUSCAR_POR_NUMERO =
            "SELECT * FROM mesa where numero = ?";
    private static final String SQL_INSERIR =
            "INSERT INTO mesa (id, numero, status) VALUES (?,?,?)";
    private static final String SQL_ALTERAR =
            "UPDATE mesa SET numero = ?, status = ? WHERE id = ?";
    private static final String SQL_EXCLUIR =
            "DELETE FROM mesa WHERE id = ?";

    public Mesa localizar(Connection conn, long codigo) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(SQL_LOCALIZAR);
        ps.setLong(1, codigo);

        ResultSet rs = ps.executeQuery();

        if (rs.first()) {
            Mesa mesa = new Mesa();
            mesa.setId(rs.getLong("id"));
            mesa.setNumero(rs.getInt("numero"));
            mesa.setStatus(rs.getString("status"));

            return mesa;
        } else {
            return null;
        }
    }

    public Mesa salvar(Mesa mesa) throws SQLException, Exception {
        if (mesa.getId() == 0) {
            return inserir(mesa);
        } else {
            return alterar(mesa);
        }
    }
    
    private Boolean validarNumeroMesa(Connection conn, Integer numeroMesa) throws SQLException{
        PreparedStatement ps = conn.prepareStatement(SQL_BUSCAR_POR_NUMERO);
        ps.setLong(1, numeroMesa);
        
        ResultSet rs = ps.executeQuery();
        
        return (rs.first());
    }    
    
    private Mesa inserir(Mesa mesa) throws SQLException, Exception {
        Connection conn = Conexao.getConnection();
        
        if(validarNumeroMesa(conn, mesa.getNumero())) {
            throw new Exception("Atenção, já existe uma mesa com o número: " + mesa.getNumero() + ". Por favor insira um novo número para a mesa");
        }

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_INSERIR,
                    PreparedStatement.RETURN_GENERATED_KEYS);
            
            ps.setNull(1, Types.NULL);
            ps.setInt(2, mesa.getNumero());
            ps.setString(3, mesa.getStatus());

            ps.executeUpdate();

            ResultSet rs = ps.getGeneratedKeys();
            rs.first();

            mesa.setId(rs.getLong("GENERATED_KEY"));

            conn.commit();
        } finally {
            conn.close();
        }

        return mesa;
    }
    
     private Mesa alterar(Mesa mesa) throws SQLException {
        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_ALTERAR);

            ps.setInt(1, mesa.getNumero());
            ps.setString(2, mesa.getStatus());
            ps.setLong(3, mesa.getId());
            
            ps.executeUpdate();

            conn.commit();
        } finally {
            conn.close();
        }

        return mesa;
    }
     
     public List listar() throws SQLException {
        List lista = new ArrayList();

        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_LISTAR);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Mesa mesa = new Mesa();
                
                mesa.setId(rs.getLong("id"));
                mesa.setNumero(rs.getInt("numero"));
                mesa.setStatus(rs.getString("status"));

                lista.add(mesa);
            }
        } finally {
            conn.close();
        }

        return lista;
    }
     
     public void excluir(Mesa mesa) throws SQLException {
        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_EXCLUIR);

            ps.setLong(1, mesa.getId());

            ps.executeUpdate();

            conn.commit();
        } finally {
            conn.close();
        }
    }
}
